﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Web.UI;

/// <summary>
/// Operation 网站业务流程类（封装所有业务方法）
/// </summary>
public class Operation
{
    public Operation()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    DataBase data = new DataBase();
    #region  根据邮箱查询
    /// <summary>
    /// 查询注册邮箱
    /// </summary>
    /// <param name="email">E-mail</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet ValidateEmail(string email)
    {
        SqlParameter[] parms = { data.MakeInParam("@email", SqlDbType.VarChar, 20, email) };
        return data.RunProcReturn("SELECT * FROM tb_user where email=@email", parms, "tb_user");
    }
    #endregion
    #region  根据用户名查询tb_user
    /// <summary>
    /// 查询注册用户
    /// </summary>
    /// <param name="email">E-mail</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet ValidateUser(string name)
    {
        SqlParameter[] parms = { data.MakeInParam("@name", SqlDbType.VarChar, 20, name) };
        return data.RunProcReturn("SELECT * FROM tb_user where name=@name", parms, "tb_user");
    }
    #endregion
    #region  用户注册
    /// <summary>
    /// 添加用户
    /// </summary>
    /// <param name="name">用户名</param>
    /// <param name="password">密码</param>
    /// <param name="type">用户类别</param>
    /// <param name="email">Email</param>
    /// <param name="tel">联系电话</param>
    /// /// <param name="qq">QQ</param>
    /// <param name="msn">MSN</param>
    public void InsertUser(string name, string password, string type, string email, string tel, string qq, string msn,string Comname,string address,string info)
    {
        //  byte checkState=0;
        SqlParameter[] parms ={ 
            data.MakeInParam("@name",SqlDbType.VarChar,10,name),
            data.MakeInParam("@password",SqlDbType.VarChar,20,password),
            data.MakeInParam("@type",SqlDbType.VarChar,20,type),
            data.MakeInParam("@email",SqlDbType.VarChar,20,email),
            data.MakeInParam("@tel",SqlDbType.VarChar,20,tel),
            data.MakeInParam("@qq",SqlDbType.VarChar,20,qq),
            data.MakeInParam("@msn",SqlDbType.VarChar,20,msn),
            data.MakeInParam("@comname",SqlDbType.VarChar,50,Comname),
            data.MakeInParam("@address",SqlDbType.VarChar,50,address),
            data.MakeInParam("@info",SqlDbType.VarChar,50,info)
        };
        int i = data.RunProc("INSERT INTO tb_user (name, password, type, email, tel,qq,msn,comname,address,info) VALUES (@name, @password, @type, @email, @tel,@qq,@msn,@comname,@address,@info)", parms);
    }
    #endregion
    #region 用户登录

    public DataSet LogonWithEmail(string email, string password)
    {
        SqlParameter[] parms ={
            data.MakeInParam("@email",SqlDbType.VarChar,20,email),
            data.MakeInParam("@password",SqlDbType.VarChar,20,password)
        };
        return data.RunProcReturn("Select * from tb_user where email=@email and password=@password", parms, "tb_user");
    }
    #endregion
    #region  激活注册账户并更新regtime
    /// <summary>
    /// 激活注册账户并更新regtime
    /// </summary>
    /// <param name="id">用户ＩＤ</param>

    public void ActiveUser(string id)
    {
        data.RunProc("UPDATE tb_user SET active = 1, regtime=getdate() WHERE (ID = " + id + ")");
    }
    #endregion
    #region  根据userid查询用户发布的信息
    /// <summary>
    /// 查询用户发布的信息
    /// </summary>
    /// <param name="userid">E-mail</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectPublicInfo(int userid, bool check)
    {
        byte checkState = 0;
        if (check) checkState = 1;
        return data.RunProcReturn("select * from tb_info where userid='" + userid + "' and checkState='" + checkState + "' ORDER BY date DESC", "tb_info");
    }
    #endregion
    #region  根据id查询用户发布的信息
    /// <summary>
    /// 查询用户发布的信息
    /// </summary>
    /// <param name="userid">ID</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectPublicInfoById(String id)
    {
        return data.RunProcReturn("select * from tb_info where ID='" + id + "' ORDER BY date DESC", "tb_info");
    }
    public DataSet SelectPublicInfoById(int id, bool check)
    {
        byte checkState = 0;
        if (check) checkState = 1;
        return data.RunProcReturn("select * from tb_info where ID='" + id + "' and checkState='" + checkState + "' ORDER BY date DESC", "tb_info");
    }
     #endregion
    #region  修改发布信息
    /// <summary>
    /// 用户修改已经发布的信息
    /// </summary>
    /// <param name="id">信息ＩＤ</param>
    /// <param name="type">信息类型</param>
    public void UpdatePublicInfo(string type, string title, string info, string linkMan, string tel,string typeid, String id)
    {
        data.RunProc("UPDATE tb_info SET type = '"+type+"',title='"+title+"',info='"+info+"',linkman='"
        + linkMan + "',tel='" + tel + "',typeid='" + typeid + "',date=getDate() WHERE (ID = " + id + ")");
    }
    #endregion
    #region  根据userid查询用户信息
    /// <summary>
    /// 查询用户的信息
    /// </summary>
    /// <param name="userid">ID</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectUserInfo(string userid)
    {
        return data.RunProcReturn("select * from tb_user where ID=" + userid, "tb_user");
    }
    #endregion
    #region  根据是否激活查询用户信息
    /// <summary>
    /// 查询用户的信息
    /// </summary>
    /// <param name="userid">ID</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectUserInfo(bool Active)
    {
        byte active = 0;
        if (Active) active = 1;
        return data.RunProcReturn("select * from tb_user where active='" + active + "' ORDER BY regtime DESC", "tb_user");
    }
    #endregion
    #region  根据type查询用户信息
    /// <summary>
    /// 查询用户的信息
    /// </summary>
    /// <param name="type">ID</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectUserInfoByType(string type)
    {
        return data.RunProcReturn("select * from tb_user where type='" + type + "' ORDER BY regtime DESC", "tb_user");
    }
    #endregion
    #region  查询所有用户信息
    /// <summary>
    /// 查询所有用户的信息
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectUserInfo()
    {
        return data.RunProcReturn("select * from tb_user ORDER BY regtime DESC", "tb_user");
    }
    #endregion 
    #region  修改用户信息
    /// <summary>
    /// 修改用户信息
    /// </summary>
    /// <param name="id">ＩＤ</param>
    /// <param name="name">用户名</param>
    /// <param name="password">密码</param>
    /// <param name="type">用户类别</param>
    /// <param name="email">Email</param>
    /// <param name="tel">联系电话</param>
    /// /// <param name="qq">QQ</param>
    /// <param name="msn">MSN</param>
    public void UpdateUserInfo(string name, string type, string email, string tel,
        string qq, string msn,string comname,string address,string info, String id)
    {
        data.RunProc("UPDATE tb_user SET type = '" + type + "',name='" + name + "',email='" + email + "',qq='"
        + qq + "',tel='" + tel + "',msn='" + msn + "',comname='" + comname + "',address='" +
        address + "',info='" + info + "' WHERE (ID = " + id + ")");
    }
    #endregion
    #region  修改用户密码
    /// <summary>
    /// 修改用户密码
    /// </summary>
    /// <param name="id">ＩＤ</param>
    /// <param name="password">密码</param>
    public void UpdateUserPwd(string pwd, String id)
    {
        data.RunProc("UPDATE tb_user SET password = '" + pwd  + "' WHERE (ID = '" + id + "')");
    }
    #endregion
    #region  删除用户

    /// <summary>
    /// 删除指定的用户
    /// </summary>
    /// <param name="id">用户ＩＤ</param>
    public void DeleteUser(string id)
    {
        data.RunProc("Delete from tb_info where userid='" + id + "'");//先删除用户发布的信息
        data.RunProc("delete from tb_rcminfo where userid='" + id + "'");//删除推荐给用户的信息
        FCKLoadedManager mg = new FCKLoadedManager(id);//删除用户文件夹
        mg.deleteUserForder();
        int d = data.RunProc("Delete from tb_user where id='" + id + "'");
    }

    #endregion
    //留言部分
    #region  添加留言信息
    /// <summary>
    /// 添加留言信息
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="message">内容</param>
    /// <param name="name">联系人</param>
    /// <param name="tel">联系电话</param>
    /// /// <param name="email">邮箱</param>
    /// <param name="infoid>针对的信息ID</param>
    public void InsertNote(string title, string message, string name, string tel, string email, string infoid)
    {
        SqlParameter[] parms ={ 
            data.MakeInParam("@title",SqlDbType.VarChar,50,title),
            data.MakeInParam("@message",SqlDbType.Text,Int32.MaxValue/2-1,message),
            data.MakeInParam("@name",SqlDbType.VarChar,50,name),
            data.MakeInParam("@tel",SqlDbType.VarChar,50,tel),
			data.MakeInParam("@email",SqlDbType.VarChar,50,email),
            data.MakeInParam("@infoid",SqlDbType.Int,32,infoid),
        };
        int i = data.RunProc("INSERT INTO tb_msg (title, message, name, tel,email,infoid) VALUES ( @title,@message,@name, @tel,@email,@infoid)", parms);
    }
    #endregion
    #region  删除留言信息

    /// <summary>
    /// 删除指定的留言信息
    /// </summary>
    /// <param name="id">留言信息ＩＤ</param>
    public void DeleteNote(string id)
    {
        int d = data.RunProc("Delete from tb_msg where id='" + id + "'");
    }

    #endregion
    #region  查询留言信息
    /// <summary>
    /// 按uid查询留言信息
    /// </summary>
    /// <param name="uid">信息发布人id</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectNote(string uid)
    {
        SqlParameter[] parms = { data.MakeInParam("@uid", SqlDbType.Int, 32, uid) };
        return data.RunProcReturn("SELECT tb_msg.* FROM tb_msg,tb_info where tb_msg.infoid=tb_info.id and tb_info.userid=@uid "+
            "ORDER BY time DESC", parms, "tb_msg");
    }
    #endregion
    #region  查询留言信息
    /// <summary>
    /// 按uid查询留言信息
    /// </summary>
    /// <param name="uid">信息发布人id</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectNote(string uid,bool isRead)
    {
        byte r = 0;
        if (isRead) r = 1;
        SqlParameter[] parms = { 
                                   data.MakeInParam("@uid", SqlDbType.Int, 32, uid) ,
                                   data.MakeInParam("@isread", SqlDbType.TinyInt, 8, r)
                               };
        return data.RunProcReturn("SELECT tb_msg.* FROM tb_msg,tb_info where tb_msg.isread=@isread and tb_msg.infoid=tb_info.id and tb_info.userid=@uid " +
            "ORDER BY time DESC", parms, "tb_msg");
    }
    #endregion
    #region  查询留言信息
    /// <summary>
    /// 按id查询留言信息
    /// </summary>
    /// <param name="id">留言id</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectNoteById(string id)
    {
        SqlParameter[] parms = { data.MakeInParam("@id", SqlDbType.Int, 32, id) };
        return data.RunProcReturn("SELECT tb_msg.* FROM tb_msg where tb_msg.id=@id " +
            "ORDER BY time DESC", parms, "tb_msg");
    }
    #endregion
    #region  修改查看状态
    /// <summary>
    /// 修改留言查看状态
    /// </summary>
    /// <param name="id">留言信息ＩＤ</param>
    /// <param name="isread">是否查看</param>
    public void UpdateNodeReadState(string id, bool Isread)
    {
        byte isread = 0;
        if (Isread) isread = 1;
       int i = data.RunProc("UPDATE tb_msg SET isread = '"+isread+"' WHERE (ID = " + id + ")");
    }
    #endregion
     #region  添加需求信息
    /// <summary>
    /// 添加需求信息
    public void InsertXQInfo(string type,string comname, string prjname, string prjman, string dept, 
        string rank, string address, string postcode,string tel,string phone,string fx,
        string email,string way_req,string compyinfo,string tec_problem,string new_product_tec,string will,string typeId,string userid)
    {
        byte typeid = Convert.ToByte(typeId);
        SqlParameter[] parms ={ 
            data.MakeInParam("@type",SqlDbType.VarChar,20,type),
            data.MakeInParam("@title",SqlDbType.VarChar,50,prjname),
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,way_req),
            data.MakeInParam("@linkMan",SqlDbType.VarChar,50,prjman),
            data.MakeInParam("@tel",SqlDbType.VarChar,50,tel),
			data.MakeInParam("@userid",SqlDbType.Int,32,userid),
            data.MakeInParam("@typeid",SqlDbType.TinyInt,8,typeid),
            data.MakeInParam("@comname",SqlDbType.VarChar,50,comname),
            data.MakeInParam("@dept",SqlDbType.VarChar,50,dept),
            data.MakeInParam("@rank",SqlDbType.VarChar,50,rank),
            data.MakeInParam("@address",SqlDbType.VarChar,50,address),
            data.MakeInParam("@postcode",SqlDbType.VarChar,50,postcode),
            data.MakeInParam("@phone",SqlDbType.VarChar,50,phone),
            data.MakeInParam("@fx",SqlDbType.VarChar,50,fx),
            data.MakeInParam("@email",SqlDbType.VarChar,50,email),
            data.MakeInParam("@tec_problem",SqlDbType.VarChar,500,tec_problem),
            data.MakeInParam("@compy_info",SqlDbType.VarChar,500,compyinfo),
            data.MakeInParam("@new_product_tec",SqlDbType.VarChar,50,new_product_tec),
            data.MakeInParam("@coopr_will",SqlDbType.VarChar,50,will)
        };
        int i = data.RunProc("INSERT INTO tb_info (type, title, info, linkman, tel,userid,typeid,comname,dept," +
         "rank,address,  postcode, phone, fx,email, tec_problem, compy_info, new_product_tec, coopr_will" +
         ") VALUES (@type, @title,@info,@linkMan, @tel,@userid,@typeid,@comname,@dept," +
         "@rank,@address,@postcode,@phone,@fx,@email,@tec_problem,@compy_info,@new_product_tec,@coopr_will)", parms);
    }
    #endregion
    #region  修改需求信息
    /// <summary>
    /// 修改需信息的审核状态
    public void UpdateXQInfo(string id,string comname, string prjname, string prjman, string dept,
        string rank, string address, string postcode, string tel, string phone, string fx,
        string email, string way_req, string compyinfo, string tec_problem, string new_product_tec, string will)
    {

        data.RunProc("UPDATE tb_info SET title='" + prjname + "',info='" + way_req + "',linkman='"
        + prjman + "',tel='" + tel + "',comname='" + comname + "',dept='" + dept + "',rank='" + rank
        + "',address='" + address + "',postcode='" + postcode + "',phone='" + phone + "',fx='" + fx
        + "',email='" + email + "',compy_info='" + compyinfo + "',tec_problem='" + tec_problem
        + "',new_product_tec='" + new_product_tec + "',coopr_will='" + will + "',date=getDate() WHERE (ID = " + id + ")");
    }
    #endregion
    /*
    #region  查询需求信息
    /// <summary>
    /// 查询需求信息
    /// </summary>
    public DataSet SelectXQInfo(string id)
    {
        SqlParameter[] parms = { data.MakeInParam("@id", SqlDbType.Int,32,id) };
        return data.RunProcReturn("SELECT * FROM tb_XQinfo where type=@type ORDER BY date DESC", parms, "tb_info");
    }
    #endregion
    #region  删除需求信息

    /// <summary>
    /// 删除指定的需求信息
    /// </summary>
    /// <param name="id">供求信息ＩＤ</param>
    public void DeleteXQInfo(string id)
    {
        int d = data.RunProc("Delete from tb_XQinfo where id='" + id + "'");
    }

    #endregion
    */
    #region  添加供求信息
    /// <summary>
    /// 添加供求信息
    /// </summary>
    /// <param name="type">信息类别</param>
    /// <param name="title">标题</param>
    /// <param name="info">内容</param>
    /// <param name="linkMan">联系人</param>
    /// <param name="tel">联系电话</param>
	/// <param name="userid>用户ID</param>
   public void InsertInfo(string type, string title, string info, string linkMan, string tel,int userid,string typeId)
    {
        byte typeid = Convert.ToByte(typeId);
        SqlParameter[] parms ={ 
            data.MakeInParam("@type",SqlDbType.VarChar,20,type),
            data.MakeInParam("@title",SqlDbType.VarChar,50,title),
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,info),
            data.MakeInParam("@linkMan",SqlDbType.VarChar,50,linkMan),
            data.MakeInParam("@tel",SqlDbType.VarChar,50,tel),
			data.MakeInParam("@userid",SqlDbType.Int,10,userid),
            data.MakeInParam("@typeid",SqlDbType.TinyInt,8,typeid),
        };
        int i = data.RunProc("INSERT INTO tb_info (type, title, info, linkman, tel,userid,typeid) VALUES (@type, @title,@info,@linkMan, @tel,@userid,@typeid)", parms);
    }
    #endregion

    #region  修改供求信息
    /// <summary>
    /// 修改供求信息的审核状态
    /// </summary>
    /// <param name="id">信息ＩＤ</param>
    /// <param name="type">信息类型</param>
    public void UpdateInfo(string id, string type)
    {

        DataSet ds = this.SelectInfo(type, Convert.ToInt32(id));
        try
        {
            bool checkState = Convert.ToBoolean(ds.Tables[0].Rows[0][6].ToString());
            int i;
            if (checkState)
            {
                i = data.RunProc("UPDATE tb_info SET checkState = 0 WHERE (ID = " + id + ")");
            }
            else
            {
                i = data.RunProc("UPDATE tb_info SET checkState = 1 WHERE (ID = " + id + ")");
            }
        }
        catch (Exception e) { }
    }
    #endregion

    #region  删除供求信息

    /// <summary>
    /// 删除指定的供求信息
    /// </summary>
    /// <param name="id">供求信息ＩＤ</param>
    public void DeleteInfo(string id)
    {
        DataSet ds = this.SelectInfo(Convert.ToInt32(id));
        string userid = ds.Tables[0].Rows[0][8].ToString();
        FCKLoadedManager mg = new FCKLoadedManager(userid);
        mg.deleteImages(ds.Tables[0].Rows[0][3].ToString());
        int d = data.RunProc("Delete from tb_info where id='" + id + "'");
    }

    #endregion

    #region  查询供求信息
    /// <summary>
    /// 按类型查询供求信息
    /// </summary>
    /// <param name="type">供求信息类型</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(string type)
    {
        SqlParameter[] parms ={ data.MakeInParam("@type", SqlDbType.VarChar, 50, type) };
        return data.RunProcReturn("SELECT * FROM tb_info where type=@type ORDER BY date DESC", parms, "tb_info");
    }
    /// <summary>
    /// 按类型和ＩＤ查询供求信息
    /// </summary>
    /// <param name="type">供求信息类型</param>
    /// <param name="id">供求信息ＩＤ</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(string type, int id)
    {
        SqlParameter[] parms ={ 
            data.MakeInParam("@type", SqlDbType.VarChar, 50, type) ,
        };
        return data.RunProcReturn("SELECT ID, type, title, info, linkman, tel, checkState, date FROM tb_info where (type=@type) AND (ID=" + id + ") ORDER BY date DESC", parms, "tb_info");
    }
    /// <summary>
    /// 按类型和pid查询供求信息
    /// </summary>
    /// <param name="type">供求信息类型</param>
    /// <param name="id">供求信息ＩＤ</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(string type, string tid,bool checkState)
    {
        byte check=0;
        if (checkState) check = 1;
        SqlParameter[] parms ={ 
            data.MakeInParam("@type", SqlDbType.VarChar, 20, type) ,
        };
        return data.RunProcReturn("select distinct tb_info.id,tb_info.type,tb_info.title,cast(tb_info.info as varchar(8000)) as info,tb_info.linkman," +
            "tb_info.tel,tb_info.checkState,tb_info.date,tb_info.userid,tb_info.typeid from tb_info,type where (type=@type) AND ((typeid=" + tid + ") OR (typeid=type.id  AND type.pid=" + tid + ")) AND (checkState='" + check +
            "')  ORDER BY date DESC", parms, "tb_info");
    }
    /// <summary>
    /// 按id查询供求信息
    /// </summary>
    /// <param name="type">供求信息类型</param>
    /// <param name="id">供求信息ＩＤ</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(int id)
    {
        return data.RunProcReturn("select * from tb_info where id="+id, "tb_info");
    }
    /// <summary>
    /// 按信息类型查询，审核和未审核信息。
    /// </summary>
    /// <param name="type">信息类型</param>
    /// <param name="checkState">True 显示审核信息 False显示未审核信息</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(string type, bool checkState)
    {
        byte check=0;
        if (checkState) check = 1;
        return data.RunProcReturn("select * from tb_info where type='" + type + "' and checkState='" + check + "' ORDER BY date DESC", "tb_info");
    }

    /// <summary>
    /// 供求信息快速检索
    /// </summary>
    /// <param name="type">信息类型</param>
    /// <param name="infoSearch">查询信息的关键字</param>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectInfo(string type, string infoSearch)
    {
        SqlParameter[] pars ={
            data.MakeInParam("@type", SqlDbType.VarChar, 20, type) ,
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,"%"+infoSearch+"%")
        };
        return data.RunProcReturn("select * from tb_info where (type=@type) and "+
            "(typeid=0 or typeid in (select id from type where enable=1)) "+
            "and (info like @info or tel like @info or linkman like @info or title like @info) and (checkstate=1) ORDER BY date DESC", pars, "tb_info");
    }

    #endregion
    /// <summary>
    /// 首页显示
    /// </summary>
    
    public DataSet SelectTbInfo(string infoType, int top)
    {

        return data.RunProcReturn("Select top " + top + " tb_Info.* from tb_Info where type='" + infoType + "'and checkState=1 "+
            "and (typeid=0 or typeid in (select id from type where enable=1)) order by date desc", "tb_Info");
       
    }

    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBind(string infoType, string infoKey, int currentPage,int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        pds.DataSource = SelectInfo(infoType, infoKey).Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBind(string infoKey, int currentPage, int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        SqlParameter[] pars ={
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,"%"+infoKey+"%")
        };
        DataSet ds = data.RunProcReturn("select * from tb_info where (checkstate=1) "+
            "and (typeid=0 or typeid in (select id from type where enable=1)) "+
            "and (info like @info or tel like @info or linkman like @info or title like @info) ORDER BY date DESC", pars, "tb_info");
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBindByTypeAndId(string type,string subtypeid, int currentPage, int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        SqlParameter[] pars ={
            data.MakeInParam("@type",SqlDbType.VarChar,20,type),
            data.MakeInParam("@typeid",SqlDbType.TinyInt,8,Convert.ToByte(subtypeid))
        };
        DataSet ds = data.RunProcReturn("select * from tb_info where type=@type and typeid=@typeid and (checkstate=1) "+
            "and (typeid=0 or typeid in (select id from type where enable=1)) ORDER BY date DESC", pars, "tb_info");
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBindByTypeTree(string type, string typeid, int currentPage, int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        string subcmd = "select id from type where PID='" + typeid + "'";
        string cmd = "select * from tb_info where type='"+type+"' and checkState=1 "+
            "and (typeid=0 or typeid in (select id from type where enable=1)) "+
            "and (typeid='" + typeid + "' or typeid in (" + subcmd +
            " union select id from type where pid in (" + subcmd + "))) order by date desc";
        DataSet ds = data.RunProcReturn(cmd, "tb_Info");//查询typeid本级及以typeid为父级的下两级的类型
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBindByTypeAndPId(string type, string typepid, int currentPage, int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        SqlParameter[] pars ={
            data.MakeInParam("@type",SqlDbType.VarChar,20,type),
            data.MakeInParam("@typepid",SqlDbType.TinyInt,8,Convert.ToByte(typepid))
        };
        DataSet ds = data.RunProcReturn("select tb_info.* from tb_info,type where tb_info.type=@type "+
            "and (typeid=0 or typeid in (select id from type where enable=1)) "+
            "and (checkstate=1) and typeid=type.id and type.pid=@typepid  ORDER BY date DESC", pars, "tb_info");
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBind(string infoKey, string staDay, string endDay, string comname, string cominfo, int currentPage, int PageSize)
    {
        PagedDataSource pds = new PagedDataSource();
        SqlParameter[] pars ={
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,"%"+infoKey+"%"),
            data.MakeInParam("@staDay",SqlDbType.VarChar,20,staDay),
            data.MakeInParam("@endDay",SqlDbType.VarChar,20,endDay),
            data.MakeInParam("@comname",SqlDbType.VarChar,50,"%"+comname+"%"),
            data.MakeInParam("@cominfo",SqlDbType.VarChar,500,"%"+cominfo+"%")
        };
        string useridsql = "(select tb_user.ID from tb_user where (comname like @comname) and ((address like @cominfo or address is null) " +
            "or (tb_user.info like @cominfo)))";
        string sql;
        sql = "select distinct tb_info.id,tb_info.type,tb_info.title,cast(tb_info.info as varchar(8000)) as info,tb_info.linkman," +
            "tb_info.tel,tb_info.checkState,tb_info.date,tb_info.userid,tb_info.typeid from tb_info where " +
            "(typeid=0 or typeid in (select id from type where enable=1)) and (checkstate=1) " +
            "and date >= @staDay and date<=@endDay and ((type='技术需求信息' and ((comname like @comname) and (compy_info like @cominfo)) " +
            "and (info like @info " + "or tel like @info or linkman like @info or title like @info or " +
            "(dept like @info or dept is null) or (rank like @info or rank is null) or (address like @info or address is null) or " +
            "(postcode like @info or postcode is null) or (phone like @info or phone is null) or (fx like @info or fx is null) or " +
            "(email like @info or email is null) or (tec_problem like @info or tec_problem is null) or (new_product_tec like @info or " +
            "new_product_tec is null) or (coopr_will like @info or coopr_will is null))) or (" +
            "(tb_info.userid in " + useridsql + ") and (tel like @info or linkman like @info or title like @info))) order by date desc";
        DataSet ds = data.RunProcReturn(sql, pars, "tb_info");
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion
    #region  分页设置绑定
    /// <summary>
    /// 绑定DataList控件，并且设置分页
    /// </summary>
    /// <param name="infoType">信息类型</param>
    /// <param name="infoKey">查询的关键字（如果为空，则查询所有）</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="PageSize">每页显示数量</param>
    /// <returns>返回PagedDataSource对象</returns>
    public PagedDataSource PageDataListBind(string infoType, string infoKey, string subTypeId, 
        string staDay, string endDay,string comname,string cominfo, int currentPage, int PageSize)
    {
        try
        {
            Convert.ToByte(subTypeId);//借助异常判断subTypeId的合法性，并返回无subTypeId条件限制的高级查询
        }catch(Exception ex)
        {
            return PageDataListBind(infoKey, staDay,endDay,comname,cominfo, currentPage,  PageSize);
        }
        PagedDataSource pds = new PagedDataSource();
        SqlParameter[] pars ={
            data.MakeInParam("@type",SqlDbType.VarChar,20,infoType),
            data.MakeInParam("@info",SqlDbType.Text,Int32.MaxValue/2-1,"%"+infoKey+"%"),
            data.MakeInParam("@typeid",SqlDbType.TinyInt,8,Convert.ToByte(subTypeId)),
            data.MakeInParam("@staDay",SqlDbType.VarChar,20,staDay),
            data.MakeInParam("@endDay",SqlDbType.VarChar,20,endDay),
            data.MakeInParam("@comname",SqlDbType.VarChar,50,"%"+comname+"%"),
            data.MakeInParam("@cominfo",SqlDbType.VarChar,500,"%"+cominfo+"%")
        };
        string useridsql = "(select tb_user.ID from tb_user where (comname like @comname) and ((address like @cominfo or address is null) " +
            "or (tb_user.info like @cominfo)))";
        string sql;
        sql = "select distinct tb_info.id,tb_info.type,tb_info.title,cast(tb_info.info as varchar(8000)) as info,tb_info.linkman," +
            "tb_info.tel,tb_info.checkState,tb_info.date,tb_info.userid,tb_info.typeid from tb_info where type=@type and (typeid=@typeid " +
            "or typeid in (select id from type where pid=@typeid))  and (typeid=0 or typeid in (select id from type where enable=1)) and (checkstate=1) "+
            "and date >= @staDay and date<=@endDay and ((type='技术需求信息' and ((comname like @comname) and (compy_info like @cominfo)) " +
            "and (info like @info " + "or tel like @info or linkman like @info or title like @info or " +
            "(dept like @info or dept is null) or (rank like @info or rank is null) or (address like @info or address is null) or " +
            "(postcode like @info or postcode is null) or (phone like @info or phone is null) or (fx like @info or fx is null) or " +
            "(email like @info or email is null) or (tec_problem like @info or tec_problem is null) or (new_product_tec like @info or " +
            "new_product_tec is null) or (coopr_will like @info or coopr_will is null))) or ("+
            "(tb_info.userid in " + useridsql + ") and (tel like @info or linkman like @info or title like @info))) order by date desc";
        DataSet ds = data.RunProcReturn(sql, pars, "tb_info");
        pds.DataSource = ds.Tables[0].DefaultView; //将查询结果绑定到分页数据源上。
        pds.AllowPaging = true;　　　　　　　　　　//允许分页
        pds.PageSize = PageSize;　　　　　　　　 　//设置每页显示的页数
        pds.CurrentPageIndex = currentPage - 1;　  //设置当前页
        return pds;
    }
    #endregion

    #region 后台登录

    public DataSet Logon(string user, string pwd)
    {
        SqlParameter[] parms ={
            data.MakeInParam("@sysName",SqlDbType.VarChar,20,user),
            data.MakeInParam("@sysPwd",SqlDbType.VarChar,20,pwd)
        };
        return data.RunProcReturn("Select * from tb_Power where sysName=@sysName and sysPwd=@sysPwd",parms, "tb_Power");
    }
    #endregion
    #region 后台登录验证

    public DataSet Logon(string user)
    {
        SqlParameter[] parms ={
            data.MakeInParam("@sysName",SqlDbType.VarChar,20,user)
        };
        return data.RunProcReturn("Select * from tb_Power where sysName=@sysName", parms, "tb_Power");
    }
    #endregion
    #region  添加管理员
    /// <summary>
    /// 添加管理员
    /// </summary>
    /// <param name="name">账号</param>
    /// <param name="pwd">密码</param>
    public void InsertPower(string name, string pwd)
    {
        SqlParameter[] parms ={ 
            data.MakeInParam("@sysName",SqlDbType.VarChar,50,name),
			data.MakeInParam("@sysPwd",SqlDbType.VarChar,50,pwd),
        };
        int i = data.RunProc("INSERT INTO tb_Power(sysName, sysPwd) VALUES (@sysName, @sysPwd)", parms);
    }
    #endregion
    #region  修改管理员密码
    /// <summary>
    /// 修改管理员密码
    /// </summary>
    /// <param name="sysName">sysName</param>
    /// <param name="password">密码</param>
    public void UpdateSysPwd(string pwd, String sysName)
    {
        data.RunProc("UPDATE tb_Power SET sysPwd = '" + pwd + "' WHERE (sysName = '" + sysName + "')");
    }
    #endregion
    #region  查找管理员
    
    public DataSet SearchAdmin(string infoKey)
    {
        SqlParameter[] pars ={
            data.MakeInParam("@info",SqlDbType.Text,50,"%"+infoKey+"%"),
        };
        return data.RunProcReturn("select * from tb_Power where sysName like @info", pars, "tb_Power");
    }
    #endregion
    #region  删除管理员
    public void DeleteAdmin(string id)
    {
        data.RunProc("delete from tb_Power where ID='" + id + "'" );
    }

    #endregion
//分类管理
    #region  添加信息分类
    /// <summary>
    /// 添加信息分类
    /// </summary>
    /// <param name="name">类标题</param>
    /// <param name="pid">父类Id</param>
    public void InsertCategory(string name, int PID,bool Enable)
    {
        byte enable = 0;
        if (Enable) enable = 1;
            SqlParameter[] parms ={ 
            data.MakeInParam("@name",SqlDbType.VarChar,20,name),
			data.MakeInParam("@PID",SqlDbType.TinyInt,8,PID),
            data.MakeInParam("@enable",SqlDbType.Bit,8,enable)
        };
            int i = data.RunProc("INSERT INTO type(name, PID,enable) VALUES (@name, @PID,@enable)", parms);
    }
    #endregion
    #region  获得所有类别
    /// <summary>
    /// 获得所有类别
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectAllCategory()
    {
        return data.RunProcReturn("select * from type","type");
    }

    #endregion
    #region  获得一级类别
    /// <summary>
    /// 获得一级类别
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectHeadCategory()
    {
        return data.RunProcReturn("select * from type where PID=0", "type");
    }

    #endregion
    #region  获得指定ID类别
    /// <summary>
    /// 获得指定ID类别
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectCategoryByID(string ID)
    {
        return data.RunProcReturn("select * from type where ID='"+ID+"'", "type");
    }

    #endregion
    #region  获得指定PID类别
    /// <summary>
    /// 获得指定PID类别
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectCategoryByPID(string PID)
    {
        return data.RunProcReturn("select * from type where PID=" + PID, "type");
    }

    #endregion
    #region  获得是否允许显示的指定PID类别
    /// <summary>
    /// 获得是否允许显示的指定PID类别
    /// </summary>
    /// <returns>返回查询结果DataSet数据集</returns>
    public DataSet SelectCategoryByPID(string PID,bool isShow)
    {
        byte show = 0;
        if (isShow) show = 1;
        return data.RunProcReturn("select * from type where PID=" + PID+" and enable='"+show+"'", "type");
    }

    #endregion
    #region  返回所有同PID的同类

    public DataSet SelectCategoryByName(string name,string pid)
    {
        return data.RunProcReturn("select * from type where name='" + name + "'"+" and PID='"+pid+"'", "type");
    }

    #endregion
    #region  修改enable
    
    public void UpdateTypeState( String id,String Active)
    {
        byte active = 0;
        if (Active.Equals("True")) active = 1;
        data.RunProc("UPDATE type SET enable = '" + active  + "' WHERE (ID = " + id + ")");
    }
    #endregion
    #region  修改类别名称

    public void UpdateTypeName(String id, String name)
    {
        data.RunProc("UPDATE type SET name = '" + name + "' WHERE (ID = " + id + ")");
    }
    #endregion
    #region  修改类别名称、显示状态

    public void UpdateTypeName(String id, String name, bool  Enable)
    {
        byte enable = 0;
        if (Enable) enable = 1;
        UpdateTypeName(id, name);
        if(!SelectCategoryByID(id).Tables[0].Rows[0][3].ToString().Equals(enable))
        setTypeEnable(id, Enable);//递归把子类也set了enable
    }
    #endregion
    private void setTypeEnable(string id, bool Enable)
    {
        byte enable = 0;
        if (Enable) enable = 1;
        data.RunProc("UPDATE type SET enable='" + enable + "' WHERE (ID = '" + id + "')");
        DataSet ds = this.SelectCategoryByPID(id);
        int count = ds.Tables[0].Rows.Count;
        int i=0;
        while (count >i)
        {
            setTypeEnable(ds.Tables[0].Rows[i][0].ToString(), Enable);
            i++;
        }
    }
    #region  删除指定类别同时删除所有子类
    /// <summary>
    /// 删除指定类别
    /// </summary>
    /// <param name="id">要删除类别的ＩＤ</param>
    public void DeleteCategory(string id)
    {
        int d = data.RunProc("Delete from type where ID=" + id + " or PID=" + id);
    }
    #endregion

    #region  查询推荐给userid用户的信息

    public DataSet SelectRecommendInfo(string userid)
    {
        return data.RunProcReturn("select distinct tb_info.id,tb_info.type,tb_info.title,cast(tb_info.info as varchar(8000)) as info,tb_info.linkman," +
            "tb_info.tel,tb_info.checkState,tb_info.date,tb_info.userid,tb_info.typeid from tb_info,tb_rcminfo,tb_user where tb_info.checkState=1 and tb_info.ID=tb_rcminfo.infoid and tb_rcminfo.userid=" + userid+" ORDER BY date DESC", "tb_info");
    }

    #endregion
    #region  查询某信息是否已经推荐给userid用户

    public DataSet SelectRecommendInfo(string userid,string infoid)
    {
        return data.RunProcReturn("select * from tb_rcminfo where infoid ='" + infoid + "' and userid='" + userid+"'", "tb_rcminfo");
    }

    #endregion
    #region  查询获得某信息的推荐的所有用户

    public DataSet SelectRecommendInfoByType(string infoid)
    {
        return data.RunProcReturn("select * from tb_rcminfo where infoid =" + infoid, "tb_rcminfo");
    }

    #endregion
    #region  查询获得某信息的推荐的所有用户

    public DataSet SelectRecommendInfoByType(string type,int top)
    {
        return data.RunProcReturn("select top " + top + " * from tb_info where tb_info.id in (select distinct infoid from tb_rcminfo) " +
            "and tb_info.type ='" + type + "' and (tb_info.typeid=0 or tb_info.typeid in (select id from type where enable=1)) order by date desc", "tb_info");
    }

    #endregion
    #region  查询所有推荐信息

    public DataSet SelectRecommendInfo(int top)
    {
        return data.RunProcReturn("select top " + top + " * from tb_info where tb_info.id in (select distinct infoid from tb_rcminfo) " +
            "and (tb_info.typeid=0 or tb_info.typeid in (select id from type where enable=1)) order by date desc", "tb_info");
    }

    #endregion

    #region  将信息推荐给userid用户

    public void InsertRecommendInfo(string userid,string infoid)
    {
        SqlParameter[] parms ={ 
            data.MakeInParam("@userid",SqlDbType.Int,32,userid),
			data.MakeInParam("@infoid",SqlDbType.Int,32,infoid),
        };
        int i = data.RunProc("INSERT INTO tb_rcminfo(userid, infoid) VALUES (@userid, @infoid)", parms);
    }

    #endregion
    #region  删除某条推荐记录

    public void DeleteRecommendInfo(string userid, string infoid)
    {
        data.RunProc("delete from tb_rcminfo where userid='"+userid+"' and infoid='"+infoid+"'");
    }

    #endregion
    #region  删除推荐给用户的信息

    public void DeleteRecommendInfo(string userid)
    {
        data.RunProc("delete from tb_rcminfo where userid='" + userid + "'");
    }

    #endregion
}
